-module(db_sql).
-include("common.hrl").
-compile(nowarn_unused_function).
%% ====================================================================
%% API functions
%% ====================================================================
-export([get_row/1,get_rows/1]).
-export([sql_execute_with_log/1]).

-export([
	getAccountRoleID/1,setAccountRoleID/2,
	getRole/1,setRole/1,
	addRoleOfflineEvent/2,getRoleOfflineEvent/1,deleteEvent/1
	]).

%% 获取日志表的表名,日志表按月来分表
%%get_log_table_name(t_gold_pay_add) ->
%%    t_gold_pay_add;
%%get_log_table_name(TableName) when erlang:is_atom(TableName) ->
%%	{{Y, M, _}, _} = erlang:localtime(),
%%	lists:flatten(io_lib:format("~w_~w_~w", [TableName,Y,M])).

getAccountRoleID(OpenID) ->
	case get_row(io_lib:format("select roleID from gAccount where openid = ~s", [quote(OpenID)])) of
		[RoleID|_] ->
			RoleID;
		_ -> 0
	end.
setAccountRoleID(OpenID,RoleID) ->
	Sql = io_lib:format("replace into gAccount values (~w,~s)",
		[
			RoleID,quote(OpenID)
		]),
	sql_execute_with_log(Sql).

getRole(RoleID) ->
	Sql = io_lib:format("select * from gRole where roleID = ~w",[RoleID]),
	case get_row(Sql) of
		[_RoleID,Name,HeadUrl,Score] ->
			#role{roleID = RoleID,roleName = binary_to_list(Name),headurl = binary_to_list(HeadUrl),score = Score};
		_ ->
			none
	end.
setRole(Role) ->
	Sql = io_lib:format("replace into gRole values (~w,~s,~s,~w)",
		[
			Role#role.roleID,quote(Role#role.roleName),quote(Role#role.headurl),Role#role.score
		]),
	sql_execute_with_log(Sql).

addRoleOfflineEvent(RoleID, Event) ->
	Sql = io_lib:format("insert into gOfflineEvent values (null, ~w, ~s)",[RoleID, to_bin(Event)]),
	sql_execute_with_log(Sql).
getRoleOfflineEvent(RoleID) ->
	Sql = io_lib:format("select id,eventData from gOfflineEvent where roleID = ~w",[RoleID]),
	RetList = get_rows(Sql),
	lists:map(fun([ID, DBEventData]) ->
		{ID,to_term(DBEventData)}
		end,RetList).
deleteEvent(EventID) ->
	sql_execute_with_log(io_lib:format("delete from gOfflineEvent where `id` = ~w",[EventID])).


%%===========================EXPORTED FUNCTION==============================
get_row(Sql) ->
	case sql_execute_with_log(Sql) of
		{ok,List} ->
			case length(List) of
				1 ->
					[List2] = List,
					List2;
				0 ->
					[];
				Num when Num > 1 ->
					[List2|_] = List,
					List2;
				_ ->
					[]
			end;
		_ ->
			[]
	end.

get_rows(Sql) ->
	case sql_execute_with_log(Sql) of
		{ok,Lists} ->
			Lists;
		_ ->
			[]
	end.

%% ====================================================================
%% LOCAL FUNCTION
%% ====================================================================
sql_execute_with_log(Sql)	->
	case emysql:execute(?DB,Sql) of
		{ok_packet, _,_,RetId,_,_,_}	->
			{ok,RetId};
		{result_packet, _SeqNum,_FieldList,Rows,_Extra} ->
			{ok,Rows};
		{error_packet, _, _,ErrCode,Reason2}	->
			?ERR("sql ****~s*****execute with err:~p,~s",[Sql,ErrCode,Reason2]),
			{error,{ErrCode,Reason2}};
		Exception ->
			?ERR("sql ****~s*****execute with err:~p~n",[Sql,Exception])
	end.

%% 传入的Sql变量为多条sql语句的集合,最好加入事务的保护,保证整个数据操作的原子性
sql_execute_sqls(Sql) ->
	Result = emysql:execute(?DB, Sql),
	lists:foldl(fun(E, CntAcc)->
						case E of
							{ok_packet, _,_,_RetId,_,_,_}	->
								CntAcc+1;
							{result_packet, _SeqNum,_FieldList,_Rows,_Extra} ->
								CntAcc+1;
							{error_packet, _, _,ErrCode,Reason2} ->
								?ERR("sql ****~s*****execute ~wth sqls with err:~p,~s",[Sql,CntAcc,ErrCode,Reason2]),
								CntAcc+1;
							Exception ->
								?ERR("sql ****~s*****execute ~wth sqls with err:~p~n",[Sql,CntAcc,Exception])
						end
					end,0, Result),
	ok.

%% ====================================================================
%% Internal functions
%% ====================================================================
%% 批量插入
make_sql_batch(Sql, Format, List) when List =/= []->
	Str = lists:foldl(fun(E,Acc) ->
							  ","++io_lib:format(Format,E)++Acc
					  end, ";", List),
	Sql++tl(Str).

make_sql_batch2(Sql, Format, List) when List =/= []->
	Str = lists:foldl(fun(E,Acc) ->
							  ","++io_lib:format(Format,E)++Acc
					  end, "", List),
	Sql++tl(Str).

%% 分段批量插入
make_sql_batch_by_piece(Sql, Format, List, PieceNum) ->
	make_sql_batch_by_piece(Sql, Format, List, PieceNum, 0, "").

make_sql_batch_by_piece(Sql, _Format, [], _PieceNum, _AccNum, Acc) ->
	if Acc == "" ->
		   ignore;
	   true ->
		   Sql2 = Sql ++ tl(Acc),
		   sql_execute_with_log(Sql2)
	end;
make_sql_batch_by_piece(Sql, Format, List, PieceNum, PieceNum, Acc) ->
	Sql2 = Sql ++ tl(Acc),
	sql_execute_with_log(Sql2),
	make_sql_batch_by_piece(Sql, Format, List, PieceNum, 0, "");
make_sql_batch_by_piece(Sql, Format, [E|List], PieceNum, AccNum, Acc) ->
	Acc2 = ","++io_lib:format(Format,E)++Acc,
	make_sql_batch_by_piece(Sql, Format, List, PieceNum, AccNum+1, Acc2).
	
	
to_term(Bin)->
	to_term(Bin,[]).
to_term(Bin, Default) ->
	case catch binary_to_term(Bin) of
		{'EXIT',_} ->
			Default;
		Term ->
			Term
	end.

to_bin(Term) ->
	quote(term_to_binary(Term)).

compress_encode(Term) ->
	zlib:compress(term_to_binary(Term)).

uncompress_decode(Bin) ->
	uncompress_decode(Bin,[]).
uncompress_decode(Bin, Default) ->
	case catch binary_to_term(zlib:uncompress(Bin)) of
		{'EXIT',_} ->
			Default;
		Term ->
			Term
	end.

datetime({{A,B,C},{D,E,F}}) ->
	io_lib:format("~w-~.2.0w-~.2.0w ~.2.0w:~.2.0w:~.2.0w",[A,B,C,D,E,F]);
datetime(Err) ->
	?ERR("datetime err:~p~n",[Err]).
date({A,B,C}) ->
	io_lib:format("~w-~.2.0w-~.2.0w",[A,B,C]);
date(Err) ->
	?ERR("date err:~p~n",[Err]).
time({A,B,C}) ->
	io_lib:format("~.2.0w:~.2.0w:~.2.0w",[A,B,C]).
minute({A,B,_C}) ->
	io_lib:format("~.2.0w:~.2.0w",[A,B]).
	

bool2int(true)->
	1;
bool2int(false)->
	0.

int2bool(1)->
	true;
int2bool(0)->
	false.

quote(String) when is_list(String) ->
    [39 | lists:reverse([39 | quote(String, [])])];	%% 39 is $'
quote(Bin) when is_binary(Bin) ->
    list_to_binary(quote(binary_to_list(Bin))).

quote2(String) when is_list(String) ->
    lists:reverse(quote(String, []));
quote2(Bin) when is_binary(Bin) ->
    list_to_binary(quote2(binary_to_list(Bin))).

quote([], Acc) ->
    Acc;
quote([0 | Rest], Acc) ->
    quote(Rest, [$0, $\\ | Acc]);
quote([10 | Rest], Acc) ->
    quote(Rest, [$n, $\\ | Acc]);
quote([13 | Rest], Acc) ->
    quote(Rest, [$r, $\\ | Acc]);
quote([$\\ | Rest], Acc) ->
    quote(Rest, [$\\ , $\\ | Acc]);
quote([39 | Rest], Acc) ->		%% 39 is $'
    quote(Rest, [39, $\\ | Acc]);	%% 39 is $'
quote([34 | Rest], Acc) ->		%% 34 is $"
    quote(Rest, [34, $\\ | Acc]);	%% 34 is $"
quote([26 | Rest], Acc) ->
    quote(Rest, [$Z, $\\ | Acc]);
quote([C | Rest], Acc) ->
    quote(Rest, [C | Acc]).